package net.cohoivang.news;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;

import net.cohoivang.core.Company;
import net.cohoivang.core.ProfileCompany;
import net.cohoivang.core.ProfileDatabase;
import net.cohoivang.user.MyLanguages;
import net.cohoivang.web.OurConnection;
import net.cohoivang.web.OurPrepareStatement;
import net.cohoivang.web.OurStatement;

public class NewsDatabase {
	
	private static NewsDatabase instance = new NewsDatabase();
	private OurConnection connection;
	
	public static NewsDatabase getInstance() {
		return instance;
	}
	
	public static void initialize(OurConnection connection) {
		instance = new NewsDatabase();
		instance.connection = connection;
	}
	
	public void loadAllNews() throws SQLException {
		System.out.println("... load all news ...");
		OurStatement stmt = connection.createOurStatement();
		ResultSet rs = stmt.executeQuery("select * from news");
		int count = 0;
		while (rs.next()) {
			
			Company company = ProfileCompany.getInstance().findCompany(rs.getInt("company_id"));
			if (company == null) {
				continue;
			}
			
			News news = company.createNews(rs.getInt("id"), rs.getInt("industry_id"), rs.getInt("sector_id"),
					rs.getDate("create_date"), rs.getString("create_by"), rs.getDate("last_update_date"),
					rs.getString("last_update_by"), rs.getInt("is_reviewed"), rs.getInt("isTop"), rs.getInt("expired_time"),
					rs.getInt("current_time"));
			if (news == null) {
				System.out.println("error news ...");
				continue;
			}
			news.setNewsCategory(NewsCategory.getNewsCategory(rs.getInt("news_category")));
			news.setTitle(rs.getString("title"));
			news.setDescription(rs.getString("description"));
			news.setContent(rs.getString("content"));
			news.setFilePath(rs.getString("file_path"));
			news.setSource(rs.getString("source"));
			news.setImage(rs.getString("image"));
			news.setLang(MyLanguages.getMyLanguages(rs.getString("lang_id")));
			
			news.setOriginalPrice(rs.getFloat("original_price"));
			news.setCurrentPrice(rs.getFloat("current_price"));
			news.setPercentSafe(rs.getFloat("percent_safe"));
			news.setChangePrice(rs.getFloat("change_price"));
			NewsManager.getInstance().putNews(news);			
		}
		
	}
	public int insertNews(int category, String title, String description, String content, String filePath, Date createDate,
			String createBy, Date lastUpdate, String lastUpdateBy, String source, String image, int industry, int sector, int company,
			int lang, long expiredTime, float originalPrice, float currentPrice, float percentSafe, float changePrice,
			int isTop, long currentTime) throws SQLException{
		String sqlPrepare =
			"INSERT INTO news (`news_category`, `title`, `description`,`content`,`file_path`,`create_date`,`create_by`" +
			"`last_update_date`, `last_update_by`, `source`, `image`, `industry_id`, `sector_id`,`company_id`,`lang_id`" +					
			"`expired_time`, `original_price`,`current_price`,`percent_safe`,`change_price`, `isTop`, `current_time`)" +
			" VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		OurPrepareStatement insertNew = connection.createOurPrepareStatement(sqlPrepare);
		
		int count = 0;
		insertNew.setInt(++count, category);
		insertNew.setString(++count, title);
		insertNew.setString(++count, description);
		insertNew.setString(++count, content);
		insertNew.setString(++count, filePath);
		insertNew.setDate(++count, createDate);
		insertNew.setString(++count, createBy);
		insertNew.setDate(++count, lastUpdate);		
		insertNew.setString(++count, lastUpdateBy);
		insertNew.setString(++count, source);
		insertNew.setString(++count, image);
		insertNew.setInt(++count, industry);
		insertNew.setInt(++count, sector);
		insertNew.setInt(++count, company);
		insertNew.setInt(++count, lang);
		insertNew.setLong(++count, expiredTime);
		insertNew.setFloat(++count, originalPrice);
		insertNew.setFloat(++count, currentPrice);
		insertNew.setFloat(++count, percentSafe);
		insertNew.setFloat(++count, changePrice);
		insertNew.setInt(++count, isTop);
		insertNew.setLong(++count, currentTime);
		
		
		insertNew.executeUpdate();
		ResultSet keys = insertNew.getGenerateKeys();
		int generatedId = -1;
		if (keys.next()) {
			generatedId = keys.getInt(1);
		}
		insertNew.close();			
		return generatedId;
	}
	
}
